﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services.Protocols;

namespace Service.DataAccess.DataAccessHelper
{
    public class Helper
    {
        private SqlConnection cnn;
        private SqlDataAdapter adap;
        public Helper()
        {
            cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["kn"].ConnectionString);
            adap = new SqlDataAdapter();
        }
        public Helper(string ten)
        {
            cnn=new SqlConnection(ConfigurationManager.ConnectionStrings[ten].ConnectionString);
            adap=new SqlDataAdapter();
        }
        private SqlConnection openCnn()
        {
            if (cnn.State == ConnectionState.Closed || cnn.State== ConnectionState.Broken)
            {
                cnn.Open();
            }
            return cnn;
        }
        // select query by store procedure//
         public DataSet SelectQuery(String sql, params SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            DataSet ds = new DataSet();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();
                adap.SelectCommand = myCommand;
                adap.Fill(ds);
            }
            catch (SqlException sqlex)
            {
                throw new SoapException("Lỗi kết nối:"+sqlex.Message,new System.Xml.XmlQualifiedName("CSDL"));
            }
            catch (ApplicationException appex)
            {
                throw new SoapException("Lỗi Chương trình:"+appex.Message,new System.Xml.XmlQualifiedName("Chương trình"));
            }
            catch (Exception ex)
            {
                throw new SoapException("Lỗi hệ thống:"+ex.Message,new System.Xml.XmlQualifiedName("Hệ thống"));
            }                     
            return ds;
        }
        //select query by text//
         public DataSet SelectQueryByText(String sql, params SqlParameter[] sqlParameter)
         {
             SqlCommand myCommand = new SqlCommand();
             DataSet ds = new DataSet();
             try
             {
                 myCommand.Connection = openCnn();
                 myCommand.CommandText = sql;
                 myCommand.CommandType = CommandType.Text;
                 myCommand.Parameters.Clear();
                 myCommand.Parameters.AddRange(sqlParameter);
                 myCommand.ExecuteNonQuery();                 
                 adap.SelectCommand = myCommand;
                 adap.Fill(ds);                 
             }
             catch (SqlException sqlex)
            {
                throw new SoapException("Lỗi kết nối:"+sqlex.Message,new System.Xml.XmlQualifiedName("CSDL"));
            }
            catch (ApplicationException appex)
            {
                throw new SoapException("Lỗi Chương trình:"+appex.Message,new System.Xml.XmlQualifiedName("Chương trình"));
            }
            catch (Exception ex)
            {
                throw new SoapException("Lỗi hệ thống:"+ex.Message,new System.Xml.XmlQualifiedName("Hệ thống"));
            }   
             return ds;
         }

        /// <method>
        /// Insert Query
        /// </method>
        public void InsertQuery(String sql, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                adap.InsertCommand = myCommand;
                myCommand.ExecuteNonQuery();
                //MessageBox.Show("Thực hiện thành công");
            }
           catch (SqlException sqlex)
            {
                throw new SoapException("Lỗi kết nối:"+sqlex.Message,new System.Xml.XmlQualifiedName("CSDL"));
            }
            catch (ApplicationException appex)
            {
                throw new SoapException("Lỗi Chương trình:"+appex.Message,new System.Xml.XmlQualifiedName("Chương trình"));
            }
            catch (Exception ex)
            {
                throw new SoapException("Lỗi hệ thống:"+ex.Message,new System.Xml.XmlQualifiedName("Hệ thống"));
            }   
            
      
        }

        /// <method>
        /// Update Query
        /// </method>
        public void UpdateQuery(String sql, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                adap.UpdateCommand = myCommand;
                myCommand.ExecuteNonQuery();
                //MessageBox.Show("Thực hiện thành công !!!");
            }
          catch (SqlException sqlex)
            {
                throw new SoapException("Lỗi kết nối:"+sqlex.Message,new System.Xml.XmlQualifiedName("CSDL"));
            }
            catch (ApplicationException appex)
            {
                throw new SoapException("Lỗi Chương trình:"+appex.Message,new System.Xml.XmlQualifiedName("Chương trình"));
            }
            catch (Exception ex)
            {
                throw new SoapException("Lỗi hệ thống:"+ex.Message,new System.Xml.XmlQualifiedName("Hệ thống"));
            }   
            
            
        }
        //delete query
        public void DeleteQuery(String sql, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                adap.DeleteCommand = myCommand;
                myCommand.ExecuteNonQuery();
                //MessageBox.Show("Thực hiện thành công !!!");
            }
            catch (SqlException sqlex)
            {
                throw new SoapException("Lỗi kết nối:"+sqlex.Message,new System.Xml.XmlQualifiedName("CSDL"));
            }
            catch (ApplicationException appex)
            {
                throw new SoapException("Lỗi Chương trình:"+appex.Message,new System.Xml.XmlQualifiedName("Chương trình"));
            }
            catch (Exception ex)
            {
                throw new SoapException("Lỗi hệ thống:"+ex.Message,new System.Xml.XmlQualifiedName("Hệ thống"));
            }                     
        }
    }
}
